package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Relation;
import pos.domain.RelationImpl;
import util.UIDGenerator;

public class JDBCRelationDAO implements IRelationDAO{

	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCRelationDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	public List<Relation> getAllRelations() {

		// SQL query for select the Relations
		String sql = "SELECT * FROM relation";
		ResultSet result = null;
		PreparedStatement stmt = null;
		List<Relation> allRelations = null;

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			allRelations = new ArrayList<Relation>();

			// Treatment of query
			while (result.next()){
				Relation relation = new RelationImpl();
				relation.setIdRelation(result.getString("idrelation"));
				relation.setSubject(result.getString("subject"));
				relation.setVerb(result.getString("verb"));
				relation.setObject(result.getString("object"));
				relation.setIdEvent(result.getString("idevent"));
				relation.setUri(result.getString("uri"));
				allRelations.add(relation);
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}
		return allRelations;
	}

	public Relation getRelationById(String idRelation) {

		Relation relation = new RelationImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Relation with that ID.
		String sql = "SELECT * FROM relation WHERE (idrelation = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idRelation);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				relation.setIdRelation(result.getString("idrelation"));
				relation.setSubject(result.getString("subject"));
				relation.setVerb(result.getString("verb"));
				relation.setObject(result.getString("object"));
				relation.setIdEvent(result.getString("idevent"));
				relation.setUri(result.getString("uri"));


			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return relation;
	}

	public List<Relation> getListRelationsByEvent(String idEvent) {

		List<Relation> listRelations = new ArrayList<Relation>();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Relation with that ID.
		String sql = "SELECT * FROM relation WHERE (idevent = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvent);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				Relation relation = new RelationImpl();
				relation.setIdRelation(result.getString("idrelation"));
				relation.setSubject(result.getString("subject"));
				relation.setVerb(result.getString("verb"));
				relation.setObject(result.getString("object"));
				relation.setUri(result.getString("uri"));

				relation.setIdEvent(idEvent);
				if(relation!=null){
					listRelations.add(relation);
				}

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return listRelations;
	}

	public Relation insertRelation(Relation relation, String idEvent) {

		PreparedStatement stmt = null;
		
		String idrelation = UIDGenerator.getInstance().getKey(); // ID for DB
		relation.setIdRelation(idrelation); // ID for object

		String sql = "INSERT INTO relation (idrelation,subject,verb,object,idevent,uri) VALUES (?,?,?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, idrelation);
			stmt.setString(2, relation.getSubject());
			stmt.setString(3, relation.getVerb());
			stmt.setString(4, relation.getObject());
			stmt.setString(5, idEvent);
			stmt.setString(6, relation.getUri());

			stmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();

		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return relation;
	}

	public Boolean existRelation(String subject, String verb, String object, String idevent) {

		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM relation WHERE (subject=?) AND (verb=?) AND (object=?) AND (idevent=?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, subject);
			stmt.setString(2, verb);
			stmt.setString(3, object);
			stmt.setString(4, idevent);
			
			
			result = stmt.executeQuery();

			if (result.next()==false) {
					res = false;
				} else {
					res = true;
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;
	}

}
